<?php
//TODO: try catch everyting
$link = mysqli_connect('localhost', 'root', '', 'chs');

if (!$link) {
	die('Connect Error (' . mysqli_connect_errno() . ') '
	. mysqli_connect_error());
}

function main($link) {
	//TODO: DEBUG FUNCTION , REMOVE THIS LATER !!!!!
	if(isset($_GET["reset"])) {
		dropTables($link); createTables($link);
	}
	
	if(isset($_POST["c"])) {
		$command=$_POST["c"];

		switch ($command) {
			//user availability check
			case 1:
				if(isset($_POST["d"])) {				
				$data=$_POST["d"];
				$user=getUser($link,htmlentities($data));
				
				if($user){
					$a = array('exists' => 'true');
					$json = json_encode($a);
					echo $json;
				}else{
					$a = array('exists' => 'false');
					$json = json_encode($a);
					echo $json;
				}					
				
				}else{
					$a = array('exists' => 'false');
					$json = json_encode($a);
					echo $json;
				}
					
				break;
			//create user request
			case 2:
				if(!isset($_POST["u"]) &&  !isset($_POST["p"]) && !isset($_POST["d"])) {
					// Send Error
					$a = array('code' => 1);
					$json = json_encode($a);
					echo $json;
				}else{
					$username = htmlentities($_POST["u"]);
					$passhash = htmlentities($_POST["p"]);
					$devicename = htmlentities($_POST["d"]);

					try{
						
						$user = insertUser($link,$username,$passhash);
						
						if($user) {
						
						$device_id = insertDevice($link, $user["id"], $devicename);
						
						if ($device_id) {
							$a = array('code' => 0);
							$json = json_encode($a);
							echo $json;
						} else {
							// Send Error
							$a = array('code' => 3);
							$json = json_encode($a);
							echo $json;
						}					
						
						}else{
						// Send Error
						$a = array('code' => 2);
						$json = json_encode($a);
						echo $json;
						}
					
					} catch (Exception $e) {
								$a = array('code' => 4, 'err' => 'PHP-Exception: '.$e->getMessage());
								$json = json_encode($a);
    							echo $json;	
					}
					
				}

				break;
			//login user request
			case 3:
				if(!isset($_POST["u"]) &&  !isset($_POST["p"]) && !isset($_POST["d"])) {
					// Send Error
					$a = array('code' => 1);
					$json = json_encode($a);
					echo $json;
				}else{
					$username = htmlentities($_POST["u"]);
					$passhash = htmlentities($_POST["p"]);
					$devicename = htmlentities($_POST["d"]);

					$user = getUser($link, $username);

					if($user) {
						if ($user["pass_hash"] == $passhash) {
							if (!getDeviceID($link, $user["id"], $devicename)) {

								$b = getDeviceNames($link, $user["id"]);

								$a = array('code' => 0, 'devices' => $b);

								$json = json_encode($a);
								echo $json;
							}	else {
								// Send Error
								$a = array('code' => 4);
								$json = json_encode($a);
								echo $json;
							}
						}	else {
							// Send Error
							$a = array('code' => 3);
							$json = json_encode($a);
							echo $json;
						}

					} else {
						// Send Error
						$a = array('code' => 2);
						$json = json_encode($a);
						echo $json;
					}
				}

				break;
				
				// Login again and create Device
				case 4:
					if(!isset($_POST["u"]) &&  !isset($_POST["p"]) && !isset($_POST["d"])) {
						// Send Error
						$a = array('code' => 1);
						$json = json_encode($a);
						echo $json;
					}else{
						$username = htmlentities($_POST["u"]);
						$passhash = htmlentities($_POST["p"]);
						$devicename = htmlentities($_POST["d"]);
				
						$user = getUser($link, $username);
				
						if($user) {
							if ($user["pass_hash"] == $passhash) {
								if (!getDeviceID($link, $user["id"], $devicename)) {

									$device_id = insertDevice($link, $user["id"], $devicename);

									if ($device_id) {
										$a = array('code' => 0);
										$json = json_encode($a);
										echo $json;
									}	else {
										// Send Error
										$a = array('code' => 5);
										$json = json_encode($a);
										echo $json;
									}
								}	else {
									// Send Error
									$a = array('code' => 4);
									$json = json_encode($a);
									echo $json;
								}
							}	else {
								// Send Error
								$a = array('code' => 3);
								$json = json_encode($a);
								echo $json;
							}

						} else {
							// Send Error
							$a = array('code' => 2);
							$json = json_encode($a);
							echo $json;
						}
					}
				
					break;

			//get devices
			case 5:
				if(!isset($_POST["u"]) &&  !isset($_POST["p"]) &&  !isset($_POST["d"])) {
							// Send Error
							$a = array('code' => 1);
							$json = json_encode($a);
							echo $json;
				}else{
					//authentication:
					$username = htmlentities($_POST["u"]);
					$passhash = htmlentities($_POST["p"]);
					$devicename = htmlentities($_POST["d"]);

					$user = getUser($link, $username);

					if($user) {
						if ($user["pass_hash"] == $passhash) {
							try{
								$b = getDeviceNamesWithoutD($link, $user["id"],$devicename);
								$a = array('code' => 0, 'devices' => $b);
								$json = json_encode($a);
								echo $json;	
							} catch (Exception $e) {
								$a = array('code' => 4, 'err' => 'PHP-Exception: '.$e->getMessage());
								$json = json_encode($a);
    							echo $json;	
							}
													
							
						}	else {
							// Send Error
							$a = array('code' => 3);
							$json = json_encode($a);
							echo $json;
						}

					} else {
						// Send Error
						$a = array('code' => 2);
						$json = json_encode($a);
						echo $json;
					}					
				}
				
				break;
			//user gets changes as answer: deleted links and new links for wished devices are sent back
			case 6:
				if(!isset($_POST["u"]) &&  !isset($_POST["p"]) && !isset($_POST["d"]) && !isset($_POST["ds"]) &&  !isset($_POST["ls"])) {
							// Send Error
							$a = array('code' => 1);
							$json = json_encode($a);
							echo $json;
				}else{
					//authentication:
					$username = htmlentities($_POST["u"]);
					$passhash = htmlentities($_POST["p"]);
					$devicename = htmlentities($_POST["d"]);

					$user = getUser($link, $username);

					if($user) {
						if ($user["pass_hash"] == $passhash) {
							
							try{
								
							//here we gather infos which need sync
							$devices=$_POST["ds"];
							$devices_last_syncs=$_POST["ls"];
							
							$devices_as_array=json_decode($devices,true);
							$devices_last_syncs_as_array=json_decode($devices_last_syncs,true);
							//TODO: Escape these arrays!
							
							$wanted_links=getNewVisitsPacked($link,$devices_as_array,$devices_last_syncs_as_array, $user["id"],$devicename);
							$deleted_links=getDeletedVisitsPacked($link,$devices_as_array,$devices_last_syncs_as_array, $user["id"]);
														
							
							$b = array('news' => $wanted_links,'deleteds' => $deleted_links);
							$a = array('code' => 0, 'results' => $b);
							$json = json_encode($a);
							echo $json;
							
							} catch (Exception $e) {
								$a = array('code' => 4, 'err' => 'PHP-Exception: '.$e->getMessage());
								$json = json_encode($a);
    							echo $json;	
							}
							
							
						}	else {
							// Send Error
							$a = array('code' => 3);
							$json = json_encode($a);
							echo $json;
						}

					} else {
						// Send Error
						$a = array('code' => 2);
						$json = json_encode($a);
						echo $json;
					}					
				}
				
				break;
				
			//user gives server new links and deleted links
			case 7:
				if(!isset($_POST["u"]) &&  !isset($_POST["p"]) && !isset($_POST["d"]) && !isset($_POST["ds"]) &&  !isset($_POST["ns"])) {
							// Send Error
							$a = array('code' => 1);
							$json = json_encode($a);
							echo $json;
				}else{
					//authentication:
					$username = htmlentities($_POST["u"]);
					$passhash = htmlentities($_POST["p"]);
					$devicename = htmlentities($_POST["d"]);
					$deleted_links =$_POST["ds"];
					$new_links =$_POST["ns"];
					
					$user = getUser($link, $username);

					if($user) {
						if ($user["pass_hash"] == $passhash) {
							
							try{
								
							//here we gather infos which need sync
							$deleted_links=$_POST["ds"];
							$new_links=$_POST["ns"];
							
							$deleted_links_as_array=json_decode($deleted_links,true);
							$new_links_as_array=json_decode($new_links,true);
							//TODO: Escape these arrays!
														
							$device_id = getDeviceID($link, $user["id"], $devicename);
							
							
							
							foreach ($deleted_links_as_array as $deleted1) {
								
								$device_name1=$link->real_escape_string($deleted1[0]);							
								$device_id1 = getDeviceID($link, $user["id"], $device_name1);
								$date=$deleted1[1];	
								//TODO: Escape							
								markVisitAsDeleted($link, $device_id1, $date, $user["id"]);
							}
							
							//throw new Exception("count:".count($new_links_as_array));
							
							foreach ($new_links_as_array as $newlink11) {
								$title= $link->real_escape_string($newlink11[0]);
								$link_url= $link->real_escape_string($newlink11[1]);
								$date= $link->real_escape_string($newlink11[2]);
								//TODO: Escape				
								addVisit($link, $title, $link_url, $date, $device_id, $user["id"]);
							}							
							
							
							$a = array('code' => 0);
							$json = json_encode($a);
							echo $json;
							
							
							} catch (Exception $e) {
								$a = array('code' => 4, 'err' => 'PHP-Exception: '.$e->getMessage());
								$json = json_encode($a);
    							echo $json;	
							}
							
						}	else {
							// Send Error
							$a = array('code' => 3);
							$json = json_encode($a);
							echo $json;
						}

					} else {
						// Send Error
						$a = array('code' => 2);
						$json = json_encode($a);
						echo $json;
					}					
				}
				
				break;
				
			default:
					
				break;
		}
	}
}

function dropTables($link) {
	$sql_query = "DROP TABLE `user2device`, `visit2date`, `visit`, `device`, `user`;";
	mysqli_multi_query($link, $sql_query);
}

function readAllVisits($link, $userid) {
	$datebegin = htmlspecialchars($_POST['datebegin'], ENT_QUOTES);
	$dateend = htmlspecialchars($_POST['dateend'], ENT_QUOTES);

	$sql_query = "SELECT `visit`.`id`, `visit`.`device_id`, `visit`.`link`, `visit`.`title`, `visit2date`.`date` FROM (`visit` INNER JOIN `visit2date` ON `visit`.`id`=`visit2date`.`visit_id`) where `visit`.`user_id`='"+$userid+"' AND `visit2date`.`date` between ".$datebegin." and ".$dateend.";";
	$stack = array();
	if (mysqli_multi_query($link, $sql_query)) {
		do {
			/* store first result set */
			if ($result = mysqli_store_result($link)) {
				while ($row = mysqli_fetch_row($result)) {
					$arr = array ('id'=>$row[0],'device_id'=>$row[1],'link'=>$row[2],'title'=>$row[3],'date'=>$row[4]);

					array_push($stack, json_encode($arr));
				}
				mysqli_free_result($result);
			}
		} while (mysqli_next_result($link));
	}
	return json_encode($stack);
}

function getUser($link, $username) {
	$sql_query = "SELECT * FROM user WHERE username='".$username."';";
	if (mysqli_multi_query($link, $sql_query)) {
		do {
			/* store first result set */
			if ($result = mysqli_store_result($link)) {
				while ($row = mysqli_fetch_row($result)) {
					return array ('id'=>$row[0],'username'=>$row[1],'pass_hash'=>$row[2]);
				}
				mysqli_free_result($result);
			}
		} while (mysqli_next_result($link));
	}
	return null;
}

function insertUser($link, $username, $passhash){
	$user = getUser($link, $username);
	
	if (!$user) {
		
		$sql_query = "INSERT INTO `user` (`username`, `pass_hash`) VALUES('".$username."','".$passhash."')";
		
		mysqli_multi_query($link, $sql_query);
		
		$user = getUser($link, $username);
		
	}

	return $user;
}

function getNewVisitsPacked($link, $devices, $devices_last_syncs,$user_id,$thisdevicename){
	$stack = array();
	
	$len=count($devices);
	for ($i=0; $i <$len ; $i++) {
		//we dont need own links as new links...		
		if(strcmp($thisdevicename, $devices[$i])!=0){
		$device_id=getDeviceID($link, $user_id, $devices[$i]);
		$start_date=$devices_last_syncs[$i];
		$visits=getVisits($link, $user_id, $device_id, $start_date, 1);
		
		if($visits)
		array_push($stack,array($devices[$i],$visits));
		}
	}	
	return $stack;
}

function markVisitAsDeleted($link,$device_id,$date,$user_id){
	$timeparts = explode(" ",microtime());
	$now = bcadd(($timeparts[0]*1000),bcmul($timeparts[1],1000));

	$sql_query = "UPDATE (visit INNER JOIN visit2date ON visit.id = visit2date.visit_id) SET visit.title='', visit.link='', visit2date.last_changed=".$now.", visit2date.status=0 WHERE visit2date.date=".$date." and visit.device_id=".$device_id." and visit.user_id=".$user_id." ;";
	mysqli_multi_query($link, $sql_query);
	return mysqli_insert_id($link);
}

function getVisitID($link, $title, $link_url, $device_id, $user_id) {
	$sql_query = "SELECT visit.id FROM visit WHERE (visit.user_id=".$user_id.") AND (visit.device_id=".$device_id.") AND (visit.link='".$link_url."')AND (visit.title='".$title."');";
	if (mysqli_multi_query($link, $sql_query)) {
		do {
			/* store first result set */
			if ($result = mysqli_store_result($link)) {
				while ($row = mysqli_fetch_row($result)) {
					return $row[0];
				}
				mysqli_free_result($result);
			}
		} while (mysqli_next_result($link));
	}
	return 0;
}

function getVisit2DateID($link, $visit_id, $date) {
	$sql_query = "SELECT visit2date.visit_id FROM visit2date WHERE (visit2date.visit_id=".$visit_id.") AND (visit2date.date=".$date.");";
	if (mysqli_multi_query($link, $sql_query)) {
		do {
			/* store first result set */
			if ($result = mysqli_store_result($link)) {
				while ($row = mysqli_fetch_row($result)) {
					return $row[0];
				}
				mysqli_free_result($result);
			}
		} while (mysqli_next_result($link));
	}
	return 0;
}

function insertvisit2date($link, $visit_id, $date) {		
	$sql_query = "INSERT INTO `visit2date` (`visit_id`, `date`, `status`) VALUES(".$visit_id.", ".$date.", 1)";	
	mysqli_multi_query($link, $sql_query);	
	
	return mysqli_insert_id($link);
}

function insertVisit($link, $title, $link_url, $device_id, $user_id) {
	$sql_query = "INSERT INTO `visit` (`user_id`, `link`, `title`, `device_id`) VALUES(".$user_id.",'".$link_url."', '".$title."',".$device_id.")";
	mysqli_multi_query($link, $sql_query);
	return mysqli_insert_id($link);
}

function addVisit($link, $title, $link_url, $date, $device_id, $user_id) {
	if(!$title)$title="";	
	if(!$link_url)$link_url="";
	
	$visit_id = getVisitID($link, $title, $link_url, $device_id, $user_id);
	if ($visit_id == 0) {
		$visit_id = insertVisit($link, $title, $link_url, $device_id, $user_id);	
		if ($visit_id != 0) {	
		insertvisit2date($link, $visit_id, $date);
		}else{
			//TODO: Remove this, it wont happen anymore
			throw new Exception('zero error:'.$title.' - '.$link_url.' - '.$date);
		}
	} else {		
		if (!getVisit2DateID($link, $visit_id, $date)) {		
			insertvisit2date($link, $visit_id, $date);
		}
	}
	
	return $visit_id;
}

function getDeletedVisitsPacked($link,$devices,$devices_last_syncs,$user_id){
$stack = array();
	
	$len=count($devices);
	for ($i=0; $i <$len ; $i++) { 
		$device_id=getDeviceID($link, $user_id, $devices[$i]);
		$start_date=$devices_last_syncs[$i];
		$visits=getDeletedVisits($link, $user_id, $device_id, $start_date);
		if($visits)
		array_push($stack,array($devices[$i],$visits));
	}	
	return $stack;
}

function getVisits($link, $user_id, $device_id,$start_date,$status){
	$sql_query = "SELECT visit.title, visit.link, visit2date.date FROM (visit INNER JOIN visit2date ON visit.id = visit2date.visit_id) WHERE (visit2date.status=".$status.") and (visit.device_id=".$device_id.") and (visit.user_id=".$user_id.") and (visit2date.date >".$start_date.");";
	$stack = array();
	if (mysqli_multi_query($link, $sql_query)) {		
		do {
			/* store first result set */
			if ($result = mysqli_store_result($link)) {
				while ($row = mysqli_fetch_row($result)) {
					array_push($stack, array($row[0], $row[1], $row[2]));
				}
				mysqli_free_result($result);
			}
		} while (mysqli_next_result($link));
	}
	return $stack;
}	
							
function getDeletedVisits($link, $user_id, $device_id,$start_date){
	$sql_query = "SELECT visit2date.date FROM (visit INNER JOIN visit2date ON visit.id = visit2date.visit_id) WHERE (visit2date.status="."0".") and (visit.device_id=".$device_id.") and (visit.user_id=".$user_id.") and (visit2date.last_changed >".$start_date.");";
	$stack = array();
	if (mysqli_multi_query($link, $sql_query)) {		
		do {
			/* store first result set */
			if ($result = mysqli_store_result($link)) {
				while ($row = mysqli_fetch_row($result)) {
					array_push($stack, $row[0]);
				}
				mysqli_free_result($result);
			}
		} while (mysqli_next_result($link));
	}
	return $stack;
}	

function getDevices($link, $user_id) {
	$sql_query = "SELECT device.id, device.device_name FROM (device INNER JOIN user2device ON device.id = user2device.device_id) WHERE (user2device.user_id=".$user_id.");";
	$stack = array();
	if (mysqli_multi_query($link, $sql_query)) {		
		do {
			/* store first result set */
			if ($result = mysqli_store_result($link)) {
				while ($row = mysqli_fetch_row($result)) {
					array_push($stack, array('id' => $row[0], 'device_name' => $row[1]));
				}
				mysqli_free_result($result);
			}
		} while (mysqli_next_result($link));
	}
	return $stack;
}
function getDeviceNames($link, $user_id) {
	$sql_query = "SELECT device.device_name FROM (device INNER JOIN user2device ON device.id = user2device.device_id) WHERE (user2device.user_id=".$user_id.");";
	$stack = array();
	if (mysqli_multi_query($link, $sql_query)) {		
		do {
			/* store first result set */
			if ($result = mysqli_store_result($link)) {
				while ($row = mysqli_fetch_row($result)) {
					array_push($stack,  $row[0]);
				}
				mysqli_free_result($result);
			}
		} while (mysqli_next_result($link));
	}
	return $stack;
}

function getDeviceNamesWithoutD($link, $user_id,$device_name) {
	$sql_query = "SELECT device.device_name FROM (device INNER JOIN user2device ON device.id = user2device.device_id) WHERE (user2device.user_id=".$user_id.") and device.device_name!='".$device_name."';";
	$stack = array();
	if (mysqli_multi_query($link, $sql_query)) {		
		do {
			/* store first result set */
			if ($result = mysqli_store_result($link)) {
				while ($row = mysqli_fetch_row($result)) {
					array_push($stack,  $row[0]);
				}
				mysqli_free_result($result);
			}
		} while (mysqli_next_result($link));
	}
	return $stack;
}

function getDeviceID($link, $user_id, $device_name) {
	$sql_query = "SELECT device.id FROM (device INNER JOIN user2device ON device.id = user2device.device_id) WHERE (user2device.user_id=".$user_id.") AND (device.device_name='".$device_name."');";
	if (mysqli_multi_query($link, $sql_query)) {
		do {
			/* store first result set */
			if ($result = mysqli_store_result($link)) {
				while ($row = mysqli_fetch_row($result)) {
					return $row[0];
				}
				mysqli_free_result($result);
			}
		} while (mysqli_next_result($link));
	}
	return 0;
}

function insertDevice($link, $user_id, $device_name){
	$device_id = getDeviceID($link, $user_id, $device_name);

	if (!$device_id) {
		$sql_query = "INSERT INTO `device` (`device_name`) VALUES('".$device_name."');";
		mysqli_multi_query($link, $sql_query);

		$device_id = mysqli_insert_id($link);

		$sql_query2 = "INSERT INTO `user2device` (`user_id`, `device_id`) VALUES(".$user_id.",".$device_id.")";
		mysqli_multi_query($link, $sql_query2);
	}

	return $device_id;
}

function createTables($link) {
	$sql_query =    "SET SQL_MODE=`NO_AUTO_VALUE_ON_ZERO`;
					CREATE TABLE IF NOT EXISTS `device` (
					  `id` bigint(20) NOT NULL AUTO_INCREMENT,
					  `device_name` text NOT NULL,
					  PRIMARY KEY (`id`)
					) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
					
					CREATE TABLE IF NOT EXISTS `user` (
					  `id` bigint(11) NOT NULL AUTO_INCREMENT,
					  `username` text NOT NULL,
					  `pass_hash` text NOT NULL,
					  PRIMARY KEY (`id`)
					) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
					
					CREATE TABLE IF NOT EXISTS `user2device` (
					  `user_id` bigint(20) NOT NULL,
					  `device_id` bigint(20) NOT NULL,
					  KEY `user_id_index` (`user_id`),
					  KEY `device_id_index` (`device_id`)
					) ENGINE=InnoDB DEFAULT CHARSET=latin1;
					
					CREATE TABLE IF NOT EXISTS `visit` (
					  `id` bigint(20) NOT NULL AUTO_INCREMENT,
					  `user_id` bigint(20) NOT NULL,
					  `link` text NOT NULL,
					  `title` text NOT NULL,
					  `device_id` bigint(20) NOT NULL,
					  PRIMARY KEY (`id`),
					  KEY `user_id_index` (`user_id`),
					  KEY `device_id_index` (`device_id`)
					) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
					
					CREATE TABLE IF NOT EXISTS `visit2date` (
					  `visit_id` bigint(20) NOT NULL,
					  `date` bigint(20) NOT NULL,
					  `status` tinyint(1) NOT NULL,
					  `last_changed` bigint(20),
					  
					  KEY `visit_id_index` (`visit_id`)
					) ENGINE=InnoDB DEFAULT CHARSET=latin1;
					
					
					ALTER TABLE `user2device` ADD CONSTRAINT `user2device_ibfk_2` FOREIGN KEY (`device_id`) REFERENCES `device` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION, ADD CONSTRAINT `user2device_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;
					
					ALTER TABLE `visit` ADD CONSTRAINT `visit_ibfk_3` FOREIGN KEY (`device_id`) REFERENCES `device` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION, ADD CONSTRAINT `visit_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;
					
					ALTER TABLE `visit2date` ADD CONSTRAINT `visit2date_ibfk_1` FOREIGN KEY (`visit_id`) REFERENCES `visit` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;";

	$result =mysqli_multi_query($link, $sql_query);

}

 //dropTables($link); createTables($link);

main($link);

//throw new Exception('test');
// addVisit($link,"testtitle", "linkurl",1320117792444,1,2);

/* close connection */
mysqli_close($link);

?>